package com.wuad.platformwuad.util;

import com.google.protobuf.ServiceException;
import com.wuad.platformwuad.common.SystemEnum;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.springframework.web.multipart.MultipartFile;

import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;


/**
 * poi操作Excel工具类
 *
 * @author wuad
 * @date 2023年08月09日 17:47
 */
@Slf4j
public class ExcelPoiUtils {

    /**
     * @function: 下载导出Excel模板，支持表头自定义内容及格式
     *
     * @param sheetName 表格Sheet名
     * @param titles 表格第一行（标题行）
     */
    public static HSSFWorkbook downloadExcelByPOIUtils(String sheetName, String[] titles) {
        // 创建一个Excel文件对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        // 在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = workbook.createSheet(sheetName);
        // 设置表头不可更改
//        sheet.createFreezePane(0,0,0,10);

        /**
         * 设置首行样式
         */
        // 创建样式
        HSSFCellStyle styleMsg = workbook.createCellStyle();
        //水平居中
        styleMsg.setAlignment(HorizontalAlignment.LEFT);
        //垂直居中
        styleMsg.setVerticalAlignment(VerticalAlignment.CENTER);
        // 背景色
//        styleMsg.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
//        styleMsg.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        // 自动换行
        styleMsg.setWrapText(true);
        // 合并首行单元格
        sheet.addMergedRegion(new CellRangeAddress(0,0,0,10));


        /**
         * 设置标题行样式(1.必填字段为红字)
         */
        HSSFCellStyle styleTitleRed = workbook.createCellStyle();
        styleTitleRed.setAlignment(HorizontalAlignment.CENTER);
        styleTitleRed.setVerticalAlignment(VerticalAlignment.CENTER);
        styleTitleRed.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        styleTitleRed.setFillPattern(FillPatternType.SOLID_FOREGROUND);


        // 设置字体颜色
        HSSFFont twoheadFontRed = workbook.createFont();
        twoheadFontRed.setColor(IndexedColors.RED.getIndex());
        styleTitleRed.setFont(twoheadFontRed);

        HSSFCellStyle styleTitleBlack = workbook.createCellStyle();
        styleTitleBlack.setAlignment(HorizontalAlignment.CENTER);
        styleTitleBlack.setVerticalAlignment(VerticalAlignment.CENTER);
        styleTitleBlack.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        styleTitleBlack.setFillPattern(FillPatternType.SOLID_FOREGROUND);

//        String[] titles = {"单行文本不超过50字", "电话号码不超过12字", "微信号6-20字", "单选多选 文字提示按要求输入",
//                "数字10位以内数字","日期格式年月日","日期时间格式：年月日时分秒","省市区提示","跟进人 提示输入姓名"};
        /**
         * 设置数据行数据格式（有效性验证）
         */
        // 校验文本长度（可用没问题）
        HSSFDataValidation testCheckData = ExcelPoiUtils.setDataSmallLength();
        sheet.addValidationData(testCheckData);
        // 电话号码（可用没问题）
        HSSFDataValidation dataPhone = ExcelPoiUtils.dataPhone();
        sheet.addValidationData(dataPhone);
        // 微信（可用没问题）
        HSSFDataValidation wxMsg = ExcelPoiUtils.wxLength();
        sheet.addValidationData(wxMsg);
        // 单选多选（提示信息，可用没问题）
        HSSFDataValidation dataCheck = ExcelPoiUtils.dataCheck();
        sheet.addValidationData(dataCheck);
        // 限制输入指定范围内的数字（可用没问题）
        HSSFDataValidation dataValidation = ExcelPoiUtils.setValidate();
        sheet.addValidationData(dataValidation);
        // 日期年月日格式限制
        // HSSFDataValidation dateVa = downloadExcelByPOIUtils.setDate();
        DataValidationHelper helper = sheet.getDataValidationHelper();
        CellRangeAddressList addressList = new CellRangeAddressList(2, 10000, 6, 6);
        DataValidationConstraint constraint = helper.createDateConstraint(DataValidationConstraint.OperatorType.BETWEEN,"1900-01-01","2099-12-31","yyyy-MM-dd");
        // 创建验证对象
        DataValidation dataValidation1 = helper.createValidation(constraint, addressList);
        //错误提示信息
        dataValidation.createErrorBox("提示","请输入[yyyy-MM-dd]格式日期");
        dataValidation.setShowErrorBox(true);
        //验证和工作簿绑定
        sheet.addValidationData(dataValidation1);

        // 日期时间
        HSSFDataValidation dataTime = ExcelPoiUtils.setDateTime();
        sheet.addValidationData(dataTime);
        // 省市区
        HSSFDataValidation concityMsg = ExcelPoiUtils.textWaring(8, "按照省/市/区格式填写");
        sheet.addValidationData(concityMsg);

//        // 下拉单选框设置
//        HSSFDataValidation selectBoxData = downloadExcelByPOIUtils.setBoxs();
//        sheet.addValidationData(selectBoxData);

        // 在sheet中添加表头第0行，就是标题行
        HSSFRow row = sheet.createRow(0);
        // 设置行高
        row.setHeight((short) 1600);
        // 单元格设置内容
        HSSFCell cellMsg = row.createCell(0);
        cellMsg.setCellValue("填写须知：\n" +
                "1、表头字段不允许编辑或删除\n" +
                "2、表头中红色字段为必填字段,黑色字段为选填字段\n" +
                "3、日期格式：YYYY-MM-DD ；日期时间格式：YYYY-MM-DD hh:mm:ss ；省市区格式：省/市/区\n" +
                "4、电话号码等联系方式存在多条数据时，单元格内用“,”隔开\n" +
                "5、表单内容填写完毕后，可使用excel的“数据-数据验证-圈释无效数据”功能，查验无效数据");
        cellMsg.setCellStyle(styleMsg);
        /**
         * 设置标题行(两行)的样式
         */
        HSSFRow row1 = sheet.createRow(1);
//        row1.setHeight((short) 1000);
//        Cell cell = row1.createCell(1);
//        cell.setCellValue("X1");
//        cell.setCellStyle(styleTitle);
        HSSFCell cellTitle = null;
        // 设置列名
        for (int i = 0; i < titles.length; i++) {
            // 取数据
//            JSONObject jsonObject = jsonArray.getJSONObject(i);
//            String fieldName = jsonObject.getString("fieldName");
//            String fieldText = jsonObject.getString("fieldText");
//            Integer fieldType = (Integer) jsonObject.get("fieldType");

            // 设置列宽
            sheet.setColumnWidth(i, 9100);
            // 单元格赋值
            cellTitle = row1.createCell(i);
            cellTitle.setCellValue(titles[i]);
            if (i % 2 != 0) {
                //设置样式
                cellTitle.setCellStyle(styleTitleRed);
            } else {
                //设置样式
                cellTitle.setCellStyle(styleTitleBlack);
            }

        }
        /**
         * 对内容行进行数据有效性验证
         */
        HSSFRow row2 = sheet.createRow(2);

        // 6.  创建内容
//        for (int i = 0, size = datas.length; i < size; i++) {
//            row = sheet.createRow(i + 1);
//            for (int j = 0, length = datas[i].length; j < length; j++) {
//                //将内容按顺序赋给对应的列对象
//                cell=row.createCell(j);
//                cell.setCellValue(datas[i][j]);
//                //居中显示
//                cell.setCellStyle(style);
//            }
//        }

        return workbook;
    }

    /**
     * 字符输入限制50字
     * @return
     */
    public static HSSFDataValidation setDataSmallLength() {
        CellRangeAddressList addressList = new CellRangeAddressList(2, 10000, 1, 1);
        DVConstraint constraint = DVConstraint.createNumericConstraint(DVConstraint.ValidationType.TEXT_LENGTH, DVConstraint.OperatorType.BETWEEN, "0","50");
        HSSFDataValidation dataValidation = new HSSFDataValidation(addressList, constraint);
        dataValidation.setSuppressDropDownArrow(false);
        dataValidation.createPromptBox("输入提示", "文本长度不超过50字");
        // 设置输入错误提示信息
        dataValidation.createErrorBox("文本长度过长", "文本长度不超过50字，请重新输入！");
        dataValidation.setShowPromptBox(true);
        return dataValidation;
    }

    /**
     * 字符输入限制500字
     * @return
     */
    public static HSSFDataValidation setDataBigLength() {
        CellRangeAddressList addressList = new CellRangeAddressList(2, 10000, 2, 2);
        DVConstraint constraint = DVConstraint.createNumericConstraint(DVConstraint.ValidationType.TEXT_LENGTH, DVConstraint.OperatorType.BETWEEN, "0","500");
        HSSFDataValidation dataValidation = new HSSFDataValidation(addressList, constraint);
        dataValidation.setSuppressDropDownArrow(false);
        dataValidation.createPromptBox("输入提示", "文本长度不超过500字");
        // 设置输入错误提示信息
        dataValidation.createErrorBox("文本长度过长", "文本长度不超过500字，请重新输入！");
        dataValidation.setShowPromptBox(true);
        return dataValidation;
    }

    /**
     * 校验电话号码
     * @return
     */
    public static HSSFDataValidation dataPhone() {
        // 创建一个规则：1-12的数字
        DVConstraint constraint = DVConstraint.createNumericConstraint(DVConstraint.ValidationType.INTEGER,
                DVConstraint.OperatorType.BETWEEN, "0", "19999999999");
        // 设定在哪个单元格生效
        CellRangeAddressList regions = new CellRangeAddressList(3, 10000, 2, 2);
        // 创建规则对象
        HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
        dataValidation.setSuppressDropDownArrow(false);
        dataValidation.createPromptBox("输入提示：", "请输入12位内的数字");
        dataValidation.createErrorBox("数据错误", "限制输入12位内的数字!");
        dataValidation.setShowPromptBox(true);
        return dataValidation;
    }

    /**
     * 微信字符输入限制6-20字
     * @return
     */
    public static HSSFDataValidation wxLength() {
        CellRangeAddressList addressList = new CellRangeAddressList(3, 10000, 3, 3);
        DVConstraint constraint = DVConstraint.createNumericConstraint(DVConstraint.ValidationType.TEXT_LENGTH, DVConstraint.OperatorType.BETWEEN, "6", "20");
        HSSFDataValidation dataValidation = new HSSFDataValidation(addressList, constraint);
        dataValidation.setSuppressDropDownArrow(false);
        dataValidation.createPromptBox("输入提示", "文本长度限制6-20字");
        // 设置输入错误提示信息
        dataValidation.createErrorBox("文本长度错误", "文本长度限制6-20字，请重新输入！");
        dataValidation.setShowPromptBox(true);
        return dataValidation;
    }

    /**
     * 下拉单选或多选文字提示
     * @return
     */
    public static HSSFDataValidation dataCheck() {
        CellRangeAddressList addressList = new CellRangeAddressList(3, 10000, 4, 4);

        DVConstraint constraint = DVConstraint.createNumericConstraint(DVConstraint.ValidationType.TEXT_LENGTH, DVConstraint.OperatorType.BETWEEN, "0", "1000");
        HSSFDataValidation dataValidation = new HSSFDataValidation(addressList, constraint);
        dataValidation.setSuppressDropDownArrow(false);
        dataValidation.createPromptBox("输入提示", "请按已设置的选项填写，多选请用英文逗号间隔每个选项");
        dataValidation.setShowPromptBox(true);
        return dataValidation;
    }

    /**
     * 日期格式限制
     * @return
     */
    public static HSSFDataValidation setDate() {
        // 作用范围（开始行，终止行，开始列，结束列）
        CellRangeAddressList addressList = new CellRangeAddressList(2, 10000, 6, 6);
        DVConstraint dvConstraint = DVConstraint.createDateConstraint(DVConstraint.OperatorType.BETWEEN, "Date(1900, 1, 1)",
                "Date(5000, 1, 1)", "yyyy-mm-dd");

        HSSFDataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
        dataValidation.setSuppressDropDownArrow(false);
        dataValidation.createPromptBox("输入提示", "请填写日期格式");
        // 设置输入错误提示信息
        dataValidation.createErrorBox("日期格式错误提示", "你输入的日期格式不符合'yyyy-mm-dd'格式规范，请重新输入！");
        dataValidation.setShowPromptBox(true);

        return dataValidation;
    }

    /**
     * 日期格式限制
     * @return
     */
    public static HSSFDataValidation setDateTime() {
        // 作用范围（开始行，终止行，开始列，结束列）
        CellRangeAddressList addressList = new CellRangeAddressList(3, 10000, 7, 7);
        DVConstraint dvConstraint = DVConstraint.createDateConstraint(DVConstraint.OperatorType.BETWEEN, "1900-01-01 00:00:00",
                "5000-01-01 00:00:00", "yyyy-MM-dd HH:mm:ss");

        HSSFDataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
        dataValidation.setSuppressDropDownArrow(false);
        dataValidation.createPromptBox("输入提示", "请按照日期格式：YYYY-MM-DD hh:mm:ss填写");
        // 设置输入错误提示信息
        dataValidation.createErrorBox("日期格式错误提示", "你输入的日期格式不符合'yyyy-MM-dd HH:mm:ss'格式规范，请重新输入！");
        dataValidation.setShowPromptBox(true);
        return dataValidation;
    }

    /**
     * 文字提示
     * @param coll
     * @param waringMsg
     * @return
     */
    public static HSSFDataValidation textWaring(int coll, String waringMsg) {
        CellRangeAddressList addressList = new CellRangeAddressList(3, 10000, coll, coll);

        DVConstraint constraint = DVConstraint.createNumericConstraint(DVConstraint.ValidationType.TEXT_LENGTH, DVConstraint.OperatorType.LESS_THAN, "0", "100");
        HSSFDataValidation dataValidation = new HSSFDataValidation(addressList, constraint);
        dataValidation.setSuppressDropDownArrow(false);
        dataValidation.createPromptBox("输入提示", waringMsg);
        dataValidation.setShowPromptBox(true);
        return dataValidation;
    }

    /**
     * 下拉框限制
     * @return
     */
    public static HSSFDataValidation setBoxs() {
        CellRangeAddressList addressList = new CellRangeAddressList(2, 10000, 4, 4);
        final String[] DATA_LIST = new String[] { "男", "女", };
        DVConstraint dvConstraint = DVConstraint.createExplicitListConstraint(DATA_LIST);

        HSSFDataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint);
        dataValidation.setSuppressDropDownArrow(false);
        dataValidation.createPromptBox("输入提示", "请从下拉列表中选择男女");
        // 设置输入错误提示信息
        dataValidation.createErrorBox("数据错误", "请选择已有数据!");
        dataValidation.setShowPromptBox(true);

        return dataValidation;
    }

    /**
     * 数字大小控制：设置单元格只能在1-20之间（short firstRow, short lastRow, short firstCol, short lastCol）
     * @return
     */
    public static HSSFDataValidation setValidate() {
        // 创建一个规则：1-100的数字
        DVConstraint constraint = DVConstraint.createNumericConstraint(DVConstraint.ValidationType.INTEGER,
                DVConstraint.OperatorType.BETWEEN, "1", "9999999999");
        // 设定在哪个单元格生效
        CellRangeAddressList regions = new CellRangeAddressList(2, 10000, 5, 5);
        // 创建规则对象
        HSSFDataValidation dataValidation = new HSSFDataValidation(regions, constraint);
        dataValidation.setSuppressDropDownArrow(false);
        dataValidation.createPromptBox("输入提示：","请输入10位内的数字");
        dataValidation.createErrorBox("数据错误", "限制输入指定范围内的数字!");
        dataValidation.setShowPromptBox(true);
        return dataValidation;
    }

    public static void readExcel(MultipartFile file) throws ServiceException {
        Map<Integer, Object> map = new HashMap<>();
        Workbook workbook = null;
        String str = "";
        try {
//            InputStream in = new FileInputStream("E:\\desktop\\导入客户信息模板测试版 .xlsx");
            InputStream in = file.getInputStream();
            try {
                workbook = WorkbookFactory.create(in);
            } catch (IOException e) {
                throw new ServiceException(SystemEnum.SYSTEM_FILE_OUT_ERR.value);
            } catch (Exception e) {
                throw new ServiceException("saveIndustry方法InvalidFormatException");
            }
            Sheet sheet = workbook.getSheetAt(0);
            Row row = sheet.getRow(1);
            // 标题总列数
            int colNum = row.getPhysicalNumberOfCells();
            log.info("Excel内数据行数：{}", colNum);
            Object[] title = new String[colNum];
            for (int i = 0; i < colNum; i++) {
                title[i] = getCellFormatValue(row.getCell((short) i));
            }
            List<String> ss = new ArrayList<>();
            for (Object s : title) {
                ss.add(s.toString());
            }
            log.info("获取Excel表格的标题：{}", ss);

            int rowNum = sheet.getLastRowNum();
            // 正文内容应该从第二行开始,第一行为表头的标题
            for (int i = 1; i <= rowNum; i++) {
                row = sheet.getRow(i);
                int j = 0;
                while (j < colNum) {
                    // 每个单元格的数据内容用"-"分割开，以后需要时用String类的replace()方法还原数据
                    // 也可以将每个单元格的数据设置到一个javabean的属性中，此时需要新建一个javabean
                    // str += getStringCellValue(row.getCell((short) j)).trim() +
                    // "-";
                    // TODO: 2023/8/10 有问题暂不可用，数据读取时不同类型数据，数字类型报错
                    str += getCellFormatValue(row.getCell((short) j)) + "-";
                    j++;
                }
                map.put(i, str);
                str = "";
            }
            System.out.println("获得Excel表格的内容:");
            for (int i = 1; i <= map.size(); i++) {
                System.out.println(map.get(i));
            }
        } catch (Exception e) {
            log.info("异常抛出：{}",e);
        }
    }

    /**
     * 根据HSSFCell类型设置数据
     *
     * @param cell
     * @return
     */
    private static Object getCellFormatValue(Cell cell) {
        Object cellvalue = "";
        if (cell != null) {
            log.info("cell:{}", cell.getCellStyle());
            log.info("单元格值1：{}", cell.getRichStringCellValue());
            log.info("单元格值2：{}", cell.getStringCellValue());
//            cellvalue = cell.getRichStringCellValue().getString();
            cell.getCellStyle();
        } else {
            cellvalue = "";
        }
        return cellvalue;
    }

}
